const executeSql = require('../utils/query.js')

exports.updCategory = async (category) => {
  let { category_id, category_name, sort, is_show, intro, add_date, admin_id } =
    category
  let sql = `update category 
      set category_name='${category_name}', is_show = ${is_show}, 
      add_date = ${add_date},intro = '${intro}',sort = ${sort}, admin_id = ${admin_id}
      where category_id = ${category_id}`
  let result = await executeSql(sql)
  return result
}

exports.getAllCategory = async () => {
  let sql = `select * from category`
  let result = await executeSql(sql)
  return result
}

exports.find = async (category_id = 0) => {
  let sql = `select * from category where category_id = ${category_id}`
  let result = await executeSql(sql)
  return result[0]
}

// 校验添加分类名是否重名
exports.checkCategoryName = async (category_name) => {
  let sql = `select category_id from category where category_name = '${category_name}'`
  let result = await executeSql(sql)
  return result
}

// 校验编辑分类名是否重名
exports.checkUpdCategoryName = async (category_id, category_name) => {
  let sql = `select category_id from category where category_name = '${category_name}' and category_id != ${category_id}`
  // console.log('sql=>', sql)
  let result = await executeSql(sql)
  // console.log('result=>', result)
  return result
}

exports.insertCategory = async (data) => {
  let { category_name, sort, is_show, intro, add_date, admin_id } = data
  let sql = `insert into category(category_name, sort, is_show, intro, add_date, admin_id) 
        values('${category_name}', ${sort}, ${is_show}, '${intro}', ${add_date}, ${admin_id})`
  let result = await executeSql(sql)
  return result
}

exports.getCategoryCount = async ({
  category_name,
  is_show,
  start_date,
  end_date,
}) => {
  let sql = `select count(category_id) as count from category where 1 `
  //  根据查询条件，有传条件，动态的拼接sql语句
  if (category_name) {
    sql += ` and category_name like '%${category_name}%'`
  }
  if (is_show) {
    sql += ` and is_show = ${is_show}`
  }

  if (start_date && !end_date) {
    sql += ` and add_date > ${start_date}`
  }

  if (!start_date && end_date) {
    sql += ` and add_date < ${end_date}`
  }

  if (start_date && end_date) {
    sql += ` and add_date between ${start_date} and ${end_date}`
  }
  let result = await executeSql(sql)
  return result[0].count
}

exports.deleteCategory = async (category_id = 0) => {
  let sql = `delete from category where category_id = ${category_id}`
  let result = await executeSql(sql)
  return result
}

exports.deleteManyCategory = async (category_ids) => {
  // category_ids => '1,2'
  let sql = `delete from category where category_id in (${category_ids})`
  let result = await executeSql(sql)
  return result
}

// 函数的参数是对象，可以通过{}解构出其属性
exports.getCategory = async ({
  page,
  pagesize,
  category_name,
  is_show,
  start_date,
  end_date,
}) => {
  let sql = `select t1.* ,t2.nickname from category  t1 left join manager t2 
            on t1.admin_id = t2.admin_id where 1 `
  // 分页条件
  let offset = (page - 1) * pagesize
  //  根据查询条件，有传条件，动态的拼接sql语句
  if (category_name) {
    sql += ` and t1.category_name like '%${category_name}%'`
  }
  if (is_show) {
    sql += ` and t1.is_show = ${is_show}`
  }

  if (start_date && !end_date) {
    sql += ` and t1.add_date > ${start_date}`
  }

  if (!start_date && end_date) {
    sql += ` and t1.add_date < ${end_date}`
  }

  if (start_date && end_date) {
    sql += ` and t1.add_date between ${start_date} and ${end_date}`
  }

  sql += ` order by t1.sort desc limit ${offset},${pagesize}`

  let result = await executeSql(sql)
  return result
}
